{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ee114857",
   "metadata": {},
   "source": [
    "## Manipulating and transforming DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8dec731c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "32d193b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "from openbb import obb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fb946053-1c67-4f2f-9e2d-d51744e90e03",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OpenBB Platform v4.4.1\n",
      "\n",
      "Utilities:\n",
      "    /account\n",
      "    /user\n",
      "    /system\n",
      "    /coverage\n",
      "\n",
      "Routers:\n",
      "    /commodity\n",
      "    /crypto\n",
      "    /currency\n",
      "    /derivatives\n",
      "    /econometrics\n",
      "    /economy\n",
      "    /equity\n",
      "    /etf\n",
      "    /fixedincome\n",
      "    /index\n",
      "    /news\n",
      "    /quantitative\n",
      "    /regulators\n",
      "    /technical\n",
      "\n",
      "Extensions:\n",
      "    - commodity@1.3.0\n",
      "    - crypto@1.4.0\n",
      "    - currency@1.4.0\n",
      "    - derivatives@1.4.0\n",
      "    - econometrics@1.4.3\n",
      "    - economy@1.4.0\n",
      "    - equity@1.4.0\n",
      "    - etf@1.4.0\n",
      "    - fixedincome@1.4.2\n",
      "    - index@1.4.0\n",
      "    - news@1.4.0\n",
      "    - quantitative@1.3.3\n",
      "    - regulators@1.4.1\n",
      "    - technical@1.3.3\n",
      "\n",
      "    - alpha_vantage@1.3.3\n",
      "    - benzinga@1.4.0\n",
      "    - biztoc@1.3.3\n",
      "    - bls@1.1.0\n",
      "    - cboe@1.3.3\n",
      "    - cftc@1.1.0\n",
      "    - ecb@1.3.3\n",
      "    - econdb@1.3.0\n",
      "    - federal_reserve@1.4.0\n",
      "    - finra@1.3.3\n",
      "    - finviz@1.2.3\n",
      "    - fmp@1.4.1\n",
      "    - fred@1.4.1\n",
      "    - government_us@1.3.3\n",
      "    - imf@1.1.0\n",
      "    - intrinio@1.4.0\n",
      "    - multpl@1.0.3\n",
      "    - nasdaq@1.3.3\n",
      "    - oecd@1.4.0\n",
      "    - polygon@1.4.0\n",
      "    - sec@1.4.1\n",
      "    - seeking_alpha@1.3.3\n",
      "    - stockgrid@1.3.3\n",
      "    - tiingo@1.4.0\n",
      "    - tmx@1.2.3\n",
      "    - tradier@1.2.3\n",
      "    - tradingeconomics@1.4.0\n",
      "    - us_eia@1.1.0\n",
      "    - wsj@1.3.3\n",
      "    - yfinance@1.4.1    \n"
     ]
    }
   ],
   "source": [
    "print(obb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0b109144",
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "obb.user.preferences.output_type = \"dataframe\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5ba3959",
   "metadata": {},
   "source": [
    "Fetches historical price data for the equity \"AAPL\" using the \"yfinance\" provider and stores it in 'asset'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "029b99c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset = obb.equity.price.historical(\"AAPL\", provider=\"yfinance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7a9f4ec2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>dividend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-03-25</th>\n",
       "      <td>170.570007</td>\n",
       "      <td>171.940002</td>\n",
       "      <td>169.449997</td>\n",
       "      <td>170.850006</td>\n",
       "      <td>54288300</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-26</th>\n",
       "      <td>170.000000</td>\n",
       "      <td>171.419998</td>\n",
       "      <td>169.580002</td>\n",
       "      <td>169.710007</td>\n",
       "      <td>57388400</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-27</th>\n",
       "      <td>170.410004</td>\n",
       "      <td>173.600006</td>\n",
       "      <td>170.110001</td>\n",
       "      <td>173.309998</td>\n",
       "      <td>60273300</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-28</th>\n",
       "      <td>171.750000</td>\n",
       "      <td>172.229996</td>\n",
       "      <td>170.509995</td>\n",
       "      <td>171.479996</td>\n",
       "      <td>65672700</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-04-01</th>\n",
       "      <td>171.190002</td>\n",
       "      <td>171.250000</td>\n",
       "      <td>169.479996</td>\n",
       "      <td>170.029999</td>\n",
       "      <td>46240500</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-18</th>\n",
       "      <td>214.160004</td>\n",
       "      <td>215.149994</td>\n",
       "      <td>211.490005</td>\n",
       "      <td>212.690002</td>\n",
       "      <td>42432400</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-19</th>\n",
       "      <td>214.220001</td>\n",
       "      <td>218.759995</td>\n",
       "      <td>213.750000</td>\n",
       "      <td>215.240005</td>\n",
       "      <td>54385400</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-20</th>\n",
       "      <td>213.990005</td>\n",
       "      <td>217.490005</td>\n",
       "      <td>212.220001</td>\n",
       "      <td>214.100006</td>\n",
       "      <td>48862900</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-21</th>\n",
       "      <td>211.559998</td>\n",
       "      <td>218.839996</td>\n",
       "      <td>211.279999</td>\n",
       "      <td>218.270004</td>\n",
       "      <td>93954500</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-24</th>\n",
       "      <td>221.000000</td>\n",
       "      <td>221.020996</td>\n",
       "      <td>218.580002</td>\n",
       "      <td>220.729996</td>\n",
       "      <td>42364298</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  open        high         low       close    volume  dividend\n",
       "date                                                                          \n",
       "2024-03-25  170.570007  171.940002  169.449997  170.850006  54288300       0.0\n",
       "2024-03-26  170.000000  171.419998  169.580002  169.710007  57388400       0.0\n",
       "2024-03-27  170.410004  173.600006  170.110001  173.309998  60273300       0.0\n",
       "2024-03-28  171.750000  172.229996  170.509995  171.479996  65672700       0.0\n",
       "2024-04-01  171.190002  171.250000  169.479996  170.029999  46240500       0.0\n",
       "...                ...         ...         ...         ...       ...       ...\n",
       "2025-03-18  214.160004  215.149994  211.490005  212.690002  42432400       0.0\n",
       "2025-03-19  214.220001  218.759995  213.750000  215.240005  54385400       0.0\n",
       "2025-03-20  213.990005  217.490005  212.220001  214.100006  48862900       0.0\n",
       "2025-03-21  211.559998  218.839996  211.279999  218.270004  93954500       0.0\n",
       "2025-03-24  221.000000  221.020996  218.580002  220.729996  42364298       0.0\n",
       "\n",
       "[250 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(asset)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e265e975",
   "metadata": {},
   "source": [
    "Fetches historical price data for the equity \"SPY\" using the \"yfinance\" provider and stores it in 'benchmark'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6faf63bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark = obb.equity.price.historical(\"SPY\", provider=\"yfinance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9d19bf66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>dividend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-03-25</th>\n",
       "      <td>519.799988</td>\n",
       "      <td>520.950012</td>\n",
       "      <td>519.609985</td>\n",
       "      <td>519.770020</td>\n",
       "      <td>48512100</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-26</th>\n",
       "      <td>521.229980</td>\n",
       "      <td>521.580017</td>\n",
       "      <td>518.400024</td>\n",
       "      <td>518.809998</td>\n",
       "      <td>65463700</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-27</th>\n",
       "      <td>521.710022</td>\n",
       "      <td>523.210022</td>\n",
       "      <td>519.489990</td>\n",
       "      <td>523.169983</td>\n",
       "      <td>82999800</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-03-28</th>\n",
       "      <td>523.210022</td>\n",
       "      <td>524.609985</td>\n",
       "      <td>522.780029</td>\n",
       "      <td>523.070007</td>\n",
       "      <td>96294900</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-04-01</th>\n",
       "      <td>523.830017</td>\n",
       "      <td>524.380005</td>\n",
       "      <td>520.969971</td>\n",
       "      <td>522.159973</td>\n",
       "      <td>62477500</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-18</th>\n",
       "      <td>564.799988</td>\n",
       "      <td>565.020020</td>\n",
       "      <td>559.059998</td>\n",
       "      <td>561.020020</td>\n",
       "      <td>66041400</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-19</th>\n",
       "      <td>562.830017</td>\n",
       "      <td>570.950012</td>\n",
       "      <td>561.630005</td>\n",
       "      <td>567.130005</td>\n",
       "      <td>66556000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-20</th>\n",
       "      <td>563.330017</td>\n",
       "      <td>570.570007</td>\n",
       "      <td>562.599976</td>\n",
       "      <td>565.489990</td>\n",
       "      <td>62958200</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-21</th>\n",
       "      <td>559.280029</td>\n",
       "      <td>564.890015</td>\n",
       "      <td>558.030029</td>\n",
       "      <td>563.979980</td>\n",
       "      <td>83666800</td>\n",
       "      <td>1.696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-03-24</th>\n",
       "      <td>570.799988</td>\n",
       "      <td>575.150024</td>\n",
       "      <td>570.200012</td>\n",
       "      <td>574.080017</td>\n",
       "      <td>58438519</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  open        high         low       close    volume  dividend\n",
       "date                                                                          \n",
       "2024-03-25  519.799988  520.950012  519.609985  519.770020  48512100     0.000\n",
       "2024-03-26  521.229980  521.580017  518.400024  518.809998  65463700     0.000\n",
       "2024-03-27  521.710022  523.210022  519.489990  523.169983  82999800     0.000\n",
       "2024-03-28  523.210022  524.609985  522.780029  523.070007  96294900     0.000\n",
       "2024-04-01  523.830017  524.380005  520.969971  522.159973  62477500     0.000\n",
       "...                ...         ...         ...         ...       ...       ...\n",
       "2025-03-18  564.799988  565.020020  559.059998  561.020020  66041400     0.000\n",
       "2025-03-19  562.830017  570.950012  561.630005  567.130005  66556000     0.000\n",
       "2025-03-20  563.330017  570.570007  562.599976  565.489990  62958200     0.000\n",
       "2025-03-21  559.280029  564.890015  558.030029  563.979980  83666800     1.696\n",
       "2025-03-24  570.799988  575.150024  570.200012  574.080017  58438519     0.000\n",
       "\n",
       "[250 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(benchmark)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32901a41",
   "metadata": {},
   "source": [
    "Updates the column names for 'asset' and 'benchmark'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "677f3a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\n",
    "    \"open\",\n",
    "    \"high\",\n",
    "    \"low\",\n",
    "    \"close\",\n",
    "    \"volume\",\n",
    "    \"dividend\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a2b44bbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset.columns = columns\n",
    "benchmark.columns = columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928ca43c",
   "metadata": {},
   "source": [
    "Adds a new column 'price_diff' that contains the difference in closing prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b31474db",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"price_diff\"] = asset.close.diff()\n",
    "benchmark[\"price_diff\"] = benchmark.close.diff()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ff5c4c",
   "metadata": {},
   "source": [
    "Adds a new column 'gain' that indicates whether the price difference is positive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c602b069",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"gain\"] = asset.price_diff > 0\n",
    "benchmark[\"gain\"] = benchmark.price_diff > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b2f582",
   "metadata": {},
   "source": [
    "Adds a new column 'symbol' with the equity symbol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ca720866",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"symbol\"] = \"AAPL\"\n",
    "benchmark[\"symbol\"] = \"SPY\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f304b6d",
   "metadata": {},
   "source": [
    "Sets the volume of the 10th row to the mean volume of rows 5 to 9 in 'asset_2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "71e51ec8",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset_2 = asset.copy()\n",
    "asset_2.at[asset_2.index[10], \"volume\"] = asset_2.volume[asset_2.index[5:10]].mean().astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "448d8e4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "46041260"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asset_2.iat[10, 4]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "467210a0",
   "metadata": {},
   "source": [
    "Concatenates 'asset' and 'asset_2' and drops duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d74bd576",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([asset, asset_2]).drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce74bcfa",
   "metadata": {},
   "source": [
    "Adds a new column 'returns' that contains the percentage change in closing prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f293cf69",
   "metadata": {},
   "outputs": [],
   "source": [
    "asset[\"returns\"] = asset[\"close\"].pct_change()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6473e04",
   "metadata": {},
   "source": [
    "Creates a pivot table from 'asset' with 'returns' as values, 'gain' as columns, and 'sum', 'mean', 'std' as aggregation functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "411486cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.pivot_table(\n",
    "    data=asset, values=\"returns\", columns=\"gain\", aggfunc=[\"sum\", \"mean\", \"std\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4812cbaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gain</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>returns</th>\n",
       "      <td>-1.262162</td>\n",
       "      <td>1.54811</td>\n",
       "      <td>-0.012021</td>\n",
       "      <td>0.010751</td>\n",
       "      <td>0.01066</td>\n",
       "      <td>0.010631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sum               mean                std          \n",
       "gain        False    True      False     True     False     True \n",
       "returns -1.262162  1.54811 -0.012021  0.010751  0.01066  0.010631"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd52e0e2",
   "metadata": {},
   "source": [
    "Concatenates 'asset' and 'benchmark' and groups by 'symbol' to calculate open, high, low, close (OHLC) prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4fc4b8d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "concated = pd.concat([asset, benchmark])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "bb835388",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = concated.groupby(\"symbol\").close.ohlc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "86b78ceb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>170.850006</td>\n",
       "      <td>259.019989</td>\n",
       "      <td>165.000000</td>\n",
       "      <td>220.729996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPY</th>\n",
       "      <td>519.770020</td>\n",
       "      <td>612.929993</td>\n",
       "      <td>495.160004</td>\n",
       "      <td>574.080017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              open        high         low       close\n",
       "symbol                                                \n",
       "AAPL    170.850006  259.019989  165.000000  220.729996\n",
       "SPY     519.770020  612.929993  495.160004  574.080017"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea41cecf",
   "metadata": {},
   "source": [
    "Fetches options chains for the symbol \"AAPL\" using the \"cboe\" provider and stores it in 'chains'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "89b44574",
   "metadata": {},
   "outputs": [],
   "source": [
    "chains = obb.derivatives.options.chains(\"AAPL\", provider=\"cboe\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d4eb1095",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>underlying_symbol</th>\n",
       "      <th>underlying_price</th>\n",
       "      <th>contract_symbol</th>\n",
       "      <th>expiration</th>\n",
       "      <th>dte</th>\n",
       "      <th>strike</th>\n",
       "      <th>option_type</th>\n",
       "      <th>open_interest</th>\n",
       "      <th>volume</th>\n",
       "      <th>theoretical_price</th>\n",
       "      <th>...</th>\n",
       "      <th>low</th>\n",
       "      <th>prev_close</th>\n",
       "      <th>change</th>\n",
       "      <th>change_percent</th>\n",
       "      <th>implied_volatility</th>\n",
       "      <th>delta</th>\n",
       "      <th>gamma</th>\n",
       "      <th>theta</th>\n",
       "      <th>vega</th>\n",
       "      <th>rho</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00100000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>call</td>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>120.5191</td>\n",
       "      <td>...</td>\n",
       "      <td>121.00</td>\n",
       "      <td>115.424999</td>\n",
       "      <td>5.575</td>\n",
       "      <td>0.048300</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328P00100000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>put</td>\n",
       "      <td>869</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0006</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.3182</td>\n",
       "      <td>-0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0006</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00110000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>call</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>110.5270</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>105.474998</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.9999</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328P00110000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>put</td>\n",
       "      <td>892</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.1649</td>\n",
       "      <td>-0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0009</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00120000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>call</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>100.5351</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>95.474998</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.9999</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2301</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00430000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>430.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>209.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>214.500000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2302</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217C00435000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>435.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.2446</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.065000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2315</td>\n",
       "      <td>0.0983</td>\n",
       "      <td>0.0021</td>\n",
       "      <td>-0.0067</td>\n",
       "      <td>0.6436</td>\n",
       "      <td>0.4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2303</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00435000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>435.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>214.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>219.525002</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2304</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217C00440000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>440.0</td>\n",
       "      <td>call</td>\n",
       "      <td>1769</td>\n",
       "      <td>370</td>\n",
       "      <td>3.0547</td>\n",
       "      <td>...</td>\n",
       "      <td>3.05</td>\n",
       "      <td>2.950000</td>\n",
       "      <td>0.110</td>\n",
       "      <td>0.037288</td>\n",
       "      <td>0.2337</td>\n",
       "      <td>0.0934</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>-0.0065</td>\n",
       "      <td>0.6205</td>\n",
       "      <td>0.4686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2305</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00440000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>440.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>219.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>224.525002</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2306 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     underlying_symbol  underlying_price      contract_symbol  expiration  \\\n",
       "0                 AAPL            220.73  AAPL250328C00100000  2025-03-28   \n",
       "1                 AAPL            220.73  AAPL250328P00100000  2025-03-28   \n",
       "2                 AAPL            220.73  AAPL250328C00110000  2025-03-28   \n",
       "3                 AAPL            220.73  AAPL250328P00110000  2025-03-28   \n",
       "4                 AAPL            220.73  AAPL250328C00120000  2025-03-28   \n",
       "...                ...               ...                  ...         ...   \n",
       "2301              AAPL            220.73  AAPL271217P00430000  2027-12-17   \n",
       "2302              AAPL            220.73  AAPL271217C00435000  2027-12-17   \n",
       "2303              AAPL            220.73  AAPL271217P00435000  2027-12-17   \n",
       "2304              AAPL            220.73  AAPL271217C00440000  2027-12-17   \n",
       "2305              AAPL            220.73  AAPL271217P00440000  2027-12-17   \n",
       "\n",
       "      dte  strike option_type  open_interest  volume  theoretical_price  ...  \\\n",
       "0       4   100.0        call             41       1           120.5191  ...   \n",
       "1       4   100.0         put            869       1             0.0006  ...   \n",
       "2       4   110.0        call              2       0           110.5270  ...   \n",
       "3       4   110.0         put            892       0             0.0009  ...   \n",
       "4       4   120.0        call             22       0           100.5351  ...   \n",
       "...   ...     ...         ...            ...     ...                ...  ...   \n",
       "2301  998   430.0         put              0       0           209.5657  ...   \n",
       "2302  998   435.0        call              0       0             3.2446  ...   \n",
       "2303  998   435.0         put              0       0           214.5657  ...   \n",
       "2304  998   440.0        call           1769     370             3.0547  ...   \n",
       "2305  998   440.0         put              0       0           219.5657  ...   \n",
       "\n",
       "         low  prev_close change  change_percent  implied_volatility   delta  \\\n",
       "0     121.00  115.424999  5.575        0.048300              0.0000  1.0000   \n",
       "1       0.01    0.005000  0.005        1.000000              2.3182 -0.0001   \n",
       "2       0.00  105.474998  0.000        0.000000              0.0000  0.9999   \n",
       "3       0.00    0.005000  0.000        0.000000              2.1649 -0.0001   \n",
       "4       0.00   95.474998  0.000        0.000000              0.0000  0.9999   \n",
       "...      ...         ...    ...             ...                 ...     ...   \n",
       "2301    0.00  214.500000  0.000        0.000000              0.0000 -1.0000   \n",
       "2302    0.00    3.065000  0.000        0.000000              0.2315  0.0983   \n",
       "2303    0.00  219.525002  0.000        0.000000              0.0000 -1.0000   \n",
       "2304    3.05    2.950000  0.110        0.037288              0.2337  0.0934   \n",
       "2305    0.00  224.525002  0.000        0.000000              0.0000 -1.0000   \n",
       "\n",
       "       gamma   theta    vega     rho  \n",
       "0     0.0000  0.0000  0.0000  0.0164  \n",
       "1     0.0000 -0.0006  0.0000  0.0000  \n",
       "2     0.0000  0.0000  0.0001  0.0180  \n",
       "3     0.0000 -0.0009  0.0001  0.0000  \n",
       "4     0.0000  0.0000  0.0001  0.0197  \n",
       "...      ...     ...     ...     ...  \n",
       "2301  0.0000 -0.0259  0.0000  0.0000  \n",
       "2302  0.0021 -0.0067  0.6436  0.4923  \n",
       "2303  0.0000 -0.0259  0.0000  0.0000  \n",
       "2304  0.0020 -0.0065  0.6205  0.4686  \n",
       "2305  0.0000 -0.0259  0.0000  0.0000  \n",
       "\n",
       "[2306 rows x 29 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(chains)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a1e8cf5",
   "metadata": {},
   "source": [
    "Extracts unique expiration dates from 'chains'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c04cfb6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "expirations = chains.expiration.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "959e2556",
   "metadata": {},
   "source": [
    "Filters 'chains' to get call options expiring on the 6th expiration date and stores it in 'calls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "92f9ede1",
   "metadata": {},
   "outputs": [],
   "source": [
    "calls = chains[(chains.option_type == \"call\") & (chains.expiration == expirations[5])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21ef4ceb",
   "metadata": {},
   "source": [
    "Filters 'chains' to get put options expiring on the 6th expiration date and stores it in 'puts'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "76e2bf01",
   "metadata": {},
   "outputs": [],
   "source": [
    "puts = chains[(chains.option_type == \"put\") & (chains.expiration == expirations[5])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b75b5d41",
   "metadata": {},
   "source": [
    "Sets the index of 'calls' and 'puts' to 'strike'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ec7d29a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "calls_strike = calls.set_index(\"strike\")\n",
    "puts_strike = puts.set_index(\"strike\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "784ed9f6",
   "metadata": {},
   "source": [
    "Joins 'calls_strike' and 'puts_strike' on the 'strike' index with a left join, adding suffixes to overlapping column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "995dafc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = calls_strike.join(puts_strike, how=\"left\", lsuffix=\"_call\", rsuffix=\"_put\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbc36c7d",
   "metadata": {},
   "source": [
    "Extracts the last trade prices of call and put options and stores them in 'prices'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "999c4e35",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = joined[[\"last_trade_price_call\", \"last_trade_price_put\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e026a361",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_trade_price_call</th>\n",
       "      <th>last_trade_price_put</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strike</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>110.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145.0</th>\n",
       "      <td>69.20</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150.0</th>\n",
       "      <td>70.44</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160.0</th>\n",
       "      <td>55.29</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180.0</th>\n",
       "      <td>34.37</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185.0</th>\n",
       "      <td>31.40</td>\n",
       "      <td>0.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190.0</th>\n",
       "      <td>32.05</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195.0</th>\n",
       "      <td>27.15</td>\n",
       "      <td>1.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200.0</th>\n",
       "      <td>23.08</td>\n",
       "      <td>1.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205.0</th>\n",
       "      <td>18.65</td>\n",
       "      <td>2.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210.0</th>\n",
       "      <td>15.60</td>\n",
       "      <td>3.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215.0</th>\n",
       "      <td>12.05</td>\n",
       "      <td>5.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220.0</th>\n",
       "      <td>8.80</td>\n",
       "      <td>7.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225.0</th>\n",
       "      <td>6.35</td>\n",
       "      <td>10.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230.0</th>\n",
       "      <td>3.95</td>\n",
       "      <td>13.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235.0</th>\n",
       "      <td>2.62</td>\n",
       "      <td>16.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240.0</th>\n",
       "      <td>1.62</td>\n",
       "      <td>21.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245.0</th>\n",
       "      <td>1.00</td>\n",
       "      <td>25.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250.0</th>\n",
       "      <td>0.62</td>\n",
       "      <td>35.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255.0</th>\n",
       "      <td>0.39</td>\n",
       "      <td>39.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260.0</th>\n",
       "      <td>0.25</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265.0</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270.0</th>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275.0</th>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280.0</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285.0</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295.0</th>\n",
       "      <td>0.04</td>\n",
       "      <td>81.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300.0</th>\n",
       "      <td>0.03</td>\n",
       "      <td>89.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        last_trade_price_call  last_trade_price_put\n",
       "strike                                             \n",
       "110.0                    0.00                  0.04\n",
       "120.0                    0.00                  0.12\n",
       "130.0                    0.00                  0.08\n",
       "140.0                    0.00                  0.11\n",
       "145.0                   69.20                  0.14\n",
       "150.0                   70.44                  0.25\n",
       "155.0                    0.00                  0.24\n",
       "160.0                   55.29                  0.26\n",
       "165.0                    0.00                  0.31\n",
       "170.0                    0.00                  0.40\n",
       "175.0                    0.00                  0.52\n",
       "180.0                   34.37                  0.65\n",
       "185.0                   31.40                  0.82\n",
       "190.0                   32.05                  1.06\n",
       "195.0                   27.15                  1.43\n",
       "200.0                   23.08                  1.99\n",
       "205.0                   18.65                  2.74\n",
       "210.0                   15.60                  3.76\n",
       "215.0                   12.05                  5.25\n",
       "220.0                    8.80                  7.60\n",
       "225.0                    6.35                 10.40\n",
       "230.0                    3.95                 13.43\n",
       "235.0                    2.62                 16.54\n",
       "240.0                    1.62                 21.47\n",
       "245.0                    1.00                 25.75\n",
       "250.0                    0.62                 35.25\n",
       "255.0                    0.39                 39.00\n",
       "260.0                    0.25                  0.00\n",
       "265.0                    0.17                  0.00\n",
       "270.0                    0.09                  0.00\n",
       "275.0                    0.09                  0.00\n",
       "280.0                    0.06                  0.00\n",
       "285.0                    0.07                  0.00\n",
       "290.0                    0.00                  0.00\n",
       "295.0                    0.04                 81.50\n",
       "300.0                    0.03                 89.05\n",
       "305.0                    0.00                  0.00\n",
       "310.0                    0.00                  0.00\n",
       "315.0                    0.00                  0.00\n",
       "320.0                    0.00                  0.00\n",
       "325.0                    0.00                  0.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5359808a",
   "metadata": {},
   "source": [
    "Adds a new column 'straddle_price' that contains the sum of last trade prices of call and put options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b5a8b07b",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices.loc[:, \"straddle_price\"] = prices.sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "fbd20a59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_trade_price_call</th>\n",
       "      <th>last_trade_price_put</th>\n",
       "      <th>straddle_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strike</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>110.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145.0</th>\n",
       "      <td>69.20</td>\n",
       "      <td>0.14</td>\n",
       "      <td>69.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150.0</th>\n",
       "      <td>70.44</td>\n",
       "      <td>0.25</td>\n",
       "      <td>70.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160.0</th>\n",
       "      <td>55.29</td>\n",
       "      <td>0.26</td>\n",
       "      <td>55.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180.0</th>\n",
       "      <td>34.37</td>\n",
       "      <td>0.65</td>\n",
       "      <td>35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185.0</th>\n",
       "      <td>31.40</td>\n",
       "      <td>0.82</td>\n",
       "      <td>32.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190.0</th>\n",
       "      <td>32.05</td>\n",
       "      <td>1.06</td>\n",
       "      <td>33.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195.0</th>\n",
       "      <td>27.15</td>\n",
       "      <td>1.43</td>\n",
       "      <td>28.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200.0</th>\n",
       "      <td>23.08</td>\n",
       "      <td>1.99</td>\n",
       "      <td>25.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205.0</th>\n",
       "      <td>18.65</td>\n",
       "      <td>2.74</td>\n",
       "      <td>21.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210.0</th>\n",
       "      <td>15.60</td>\n",
       "      <td>3.76</td>\n",
       "      <td>19.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215.0</th>\n",
       "      <td>12.05</td>\n",
       "      <td>5.25</td>\n",
       "      <td>17.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220.0</th>\n",
       "      <td>8.80</td>\n",
       "      <td>7.60</td>\n",
       "      <td>16.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225.0</th>\n",
       "      <td>6.35</td>\n",
       "      <td>10.40</td>\n",
       "      <td>16.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230.0</th>\n",
       "      <td>3.95</td>\n",
       "      <td>13.43</td>\n",
       "      <td>17.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235.0</th>\n",
       "      <td>2.62</td>\n",
       "      <td>16.54</td>\n",
       "      <td>19.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240.0</th>\n",
       "      <td>1.62</td>\n",
       "      <td>21.47</td>\n",
       "      <td>23.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245.0</th>\n",
       "      <td>1.00</td>\n",
       "      <td>25.75</td>\n",
       "      <td>26.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250.0</th>\n",
       "      <td>0.62</td>\n",
       "      <td>35.25</td>\n",
       "      <td>35.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255.0</th>\n",
       "      <td>0.39</td>\n",
       "      <td>39.00</td>\n",
       "      <td>39.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260.0</th>\n",
       "      <td>0.25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265.0</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270.0</th>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275.0</th>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280.0</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285.0</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295.0</th>\n",
       "      <td>0.04</td>\n",
       "      <td>81.50</td>\n",
       "      <td>81.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300.0</th>\n",
       "      <td>0.03</td>\n",
       "      <td>89.05</td>\n",
       "      <td>89.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325.0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        last_trade_price_call  last_trade_price_put  straddle_price\n",
       "strike                                                             \n",
       "110.0                    0.00                  0.04            0.04\n",
       "120.0                    0.00                  0.12            0.12\n",
       "130.0                    0.00                  0.08            0.08\n",
       "140.0                    0.00                  0.11            0.11\n",
       "145.0                   69.20                  0.14           69.34\n",
       "150.0                   70.44                  0.25           70.69\n",
       "155.0                    0.00                  0.24            0.24\n",
       "160.0                   55.29                  0.26           55.55\n",
       "165.0                    0.00                  0.31            0.31\n",
       "170.0                    0.00                  0.40            0.40\n",
       "175.0                    0.00                  0.52            0.52\n",
       "180.0                   34.37                  0.65           35.02\n",
       "185.0                   31.40                  0.82           32.22\n",
       "190.0                   32.05                  1.06           33.11\n",
       "195.0                   27.15                  1.43           28.58\n",
       "200.0                   23.08                  1.99           25.07\n",
       "205.0                   18.65                  2.74           21.39\n",
       "210.0                   15.60                  3.76           19.36\n",
       "215.0                   12.05                  5.25           17.30\n",
       "220.0                    8.80                  7.60           16.40\n",
       "225.0                    6.35                 10.40           16.75\n",
       "230.0                    3.95                 13.43           17.38\n",
       "235.0                    2.62                 16.54           19.16\n",
       "240.0                    1.62                 21.47           23.09\n",
       "245.0                    1.00                 25.75           26.75\n",
       "250.0                    0.62                 35.25           35.87\n",
       "255.0                    0.39                 39.00           39.39\n",
       "260.0                    0.25                  0.00            0.25\n",
       "265.0                    0.17                  0.00            0.17\n",
       "270.0                    0.09                  0.00            0.09\n",
       "275.0                    0.09                  0.00            0.09\n",
       "280.0                    0.06                  0.00            0.06\n",
       "285.0                    0.07                  0.00            0.07\n",
       "290.0                    0.00                  0.00            0.00\n",
       "295.0                    0.04                 81.50           81.54\n",
       "300.0                    0.03                 89.05           89.08\n",
       "305.0                    0.00                  0.00            0.00\n",
       "310.0                    0.00                  0.00            0.00\n",
       "315.0                    0.00                  0.00            0.00\n",
       "320.0                    0.00                  0.00            0.00\n",
       "325.0                    0.00                  0.00            0.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dfeb441",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type, strike, and expiration to calculate the sum of open interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "80c9245d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\", \"strike\", \"expiration\"]).open_interest.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d57cdaf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "option_type  strike  expiration\n",
       "call         5.0     2025-06-20    756\n",
       "                     2025-09-19     17\n",
       "                     2025-12-19    208\n",
       "                     2026-01-16     87\n",
       "                     2026-06-18     14\n",
       "                                  ... \n",
       "put          440.0   2027-06-17      0\n",
       "                     2027-12-17      0\n",
       "             450.0   2026-12-18      0\n",
       "                     2027-01-15      0\n",
       "                     2027-06-17      0\n",
       "Name: open_interest, Length: 2306, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df6f0d37",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type, strike, and expiration to calculate the max last trade price and the sum of open interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5d641858",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\", \"strike\", \"expiration\"]).agg(\n",
    "    {\"last_trade_price\": \"max\", \"open_interest\": \"sum\"}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "fc2d706e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>last_trade_price</th>\n",
       "      <th>open_interest</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>option_type</th>\n",
       "      <th>strike</th>\n",
       "      <th>expiration</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">call</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">5.0</th>\n",
       "      <th>2025-06-20</th>\n",
       "      <td>215.26</td>\n",
       "      <td>756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-09-19</th>\n",
       "      <td>215.21</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-12-19</th>\n",
       "      <td>215.37</td>\n",
       "      <td>208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2026-01-16</th>\n",
       "      <td>214.68</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2026-06-18</th>\n",
       "      <td>208.05</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">put</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">440.0</th>\n",
       "      <th>2027-06-17</th>\n",
       "      <td>197.65</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2027-12-17</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">450.0</th>\n",
       "      <th>2026-12-18</th>\n",
       "      <td>210.76</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2027-01-15</th>\n",
       "      <td>238.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2027-06-17</th>\n",
       "      <td>207.60</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2306 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               last_trade_price  open_interest\n",
       "option_type strike expiration                                 \n",
       "call        5.0    2025-06-20            215.26            756\n",
       "                   2025-09-19            215.21             17\n",
       "                   2025-12-19            215.37            208\n",
       "                   2026-01-16            214.68             87\n",
       "                   2026-06-18            208.05             14\n",
       "...                                         ...            ...\n",
       "put         440.0  2027-06-17            197.65              0\n",
       "                   2027-12-17              0.00              0\n",
       "            450.0  2026-12-18            210.76              0\n",
       "                   2027-01-15            238.00              0\n",
       "                   2027-06-17            207.60              0\n",
       "\n",
       "[2306 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7d7f691",
   "metadata": {},
   "source": [
    "Groups 'chains' by option type and calculates the mean bid-ask spread for each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9aa53222",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = chains.groupby([\"option_type\"]).apply(\n",
    "    lambda x: (x[\"ask\"] - x[\"bid\"]).mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "f2b475ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "option_type\n",
       "call    1.292220\n",
       "put     0.945091\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92c4c7e7",
   "metadata": {},
   "source": [
    "Standardizes the last trade prices by subtracting the mean and dividing by the standard deviation within each expiration group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d146f7a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       3.647956\n",
       "1      -0.701411\n",
       "2       2.999092\n",
       "3      -0.701411\n",
       "4       2.704677\n",
       "          ...   \n",
       "2301   -0.752972\n",
       "2302   -0.680155\n",
       "2303   -0.752972\n",
       "2304   -0.676137\n",
       "2305   -0.752972\n",
       "Name: last_trade_price, Length: 2306, dtype: float64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    chains.groupby(\"expiration\").last_trade_price.transform(\n",
    "        lambda x: (x - x.mean()) / x.std()\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "cbe17a7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>underlying_symbol</th>\n",
       "      <th>underlying_price</th>\n",
       "      <th>contract_symbol</th>\n",
       "      <th>expiration</th>\n",
       "      <th>dte</th>\n",
       "      <th>strike</th>\n",
       "      <th>option_type</th>\n",
       "      <th>open_interest</th>\n",
       "      <th>volume</th>\n",
       "      <th>theoretical_price</th>\n",
       "      <th>...</th>\n",
       "      <th>low</th>\n",
       "      <th>prev_close</th>\n",
       "      <th>change</th>\n",
       "      <th>change_percent</th>\n",
       "      <th>implied_volatility</th>\n",
       "      <th>delta</th>\n",
       "      <th>gamma</th>\n",
       "      <th>theta</th>\n",
       "      <th>vega</th>\n",
       "      <th>rho</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00100000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>call</td>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>120.5191</td>\n",
       "      <td>...</td>\n",
       "      <td>121.00</td>\n",
       "      <td>115.424999</td>\n",
       "      <td>5.575</td>\n",
       "      <td>0.048300</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328P00100000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>put</td>\n",
       "      <td>869</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0006</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.3182</td>\n",
       "      <td>-0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0006</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00110000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>call</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>110.5270</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>105.474998</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.9999</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328P00110000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>put</td>\n",
       "      <td>892</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.1649</td>\n",
       "      <td>-0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0009</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL250328C00120000</td>\n",
       "      <td>2025-03-28</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>call</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>100.5351</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>95.474998</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.9999</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2301</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00430000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>430.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>209.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>214.500000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2302</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217C00435000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>435.0</td>\n",
       "      <td>call</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.2446</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.065000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2315</td>\n",
       "      <td>0.0983</td>\n",
       "      <td>0.0021</td>\n",
       "      <td>-0.0067</td>\n",
       "      <td>0.6436</td>\n",
       "      <td>0.4923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2303</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00435000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>435.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>214.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>219.525002</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2304</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217C00440000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>440.0</td>\n",
       "      <td>call</td>\n",
       "      <td>1769</td>\n",
       "      <td>370</td>\n",
       "      <td>3.0547</td>\n",
       "      <td>...</td>\n",
       "      <td>3.05</td>\n",
       "      <td>2.950000</td>\n",
       "      <td>0.110</td>\n",
       "      <td>0.037288</td>\n",
       "      <td>0.2337</td>\n",
       "      <td>0.0934</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>-0.0065</td>\n",
       "      <td>0.6205</td>\n",
       "      <td>0.4686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2305</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>220.73</td>\n",
       "      <td>AAPL271217P00440000</td>\n",
       "      <td>2027-12-17</td>\n",
       "      <td>998</td>\n",
       "      <td>440.0</td>\n",
       "      <td>put</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>219.5657</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>224.525002</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-0.0259</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2306 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     underlying_symbol  underlying_price      contract_symbol  expiration  \\\n",
       "0                 AAPL            220.73  AAPL250328C00100000  2025-03-28   \n",
       "1                 AAPL            220.73  AAPL250328P00100000  2025-03-28   \n",
       "2                 AAPL            220.73  AAPL250328C00110000  2025-03-28   \n",
       "3                 AAPL            220.73  AAPL250328P00110000  2025-03-28   \n",
       "4                 AAPL            220.73  AAPL250328C00120000  2025-03-28   \n",
       "...                ...               ...                  ...         ...   \n",
       "2301              AAPL            220.73  AAPL271217P00430000  2027-12-17   \n",
       "2302              AAPL            220.73  AAPL271217C00435000  2027-12-17   \n",
       "2303              AAPL            220.73  AAPL271217P00435000  2027-12-17   \n",
       "2304              AAPL            220.73  AAPL271217C00440000  2027-12-17   \n",
       "2305              AAPL            220.73  AAPL271217P00440000  2027-12-17   \n",
       "\n",
       "      dte  strike option_type  open_interest  volume  theoretical_price  ...  \\\n",
       "0       4   100.0        call             41       1           120.5191  ...   \n",
       "1       4   100.0         put            869       1             0.0006  ...   \n",
       "2       4   110.0        call              2       0           110.5270  ...   \n",
       "3       4   110.0         put            892       0             0.0009  ...   \n",
       "4       4   120.0        call             22       0           100.5351  ...   \n",
       "...   ...     ...         ...            ...     ...                ...  ...   \n",
       "2301  998   430.0         put              0       0           209.5657  ...   \n",
       "2302  998   435.0        call              0       0             3.2446  ...   \n",
       "2303  998   435.0         put              0       0           214.5657  ...   \n",
       "2304  998   440.0        call           1769     370             3.0547  ...   \n",
       "2305  998   440.0         put              0       0           219.5657  ...   \n",
       "\n",
       "         low  prev_close change  change_percent  implied_volatility   delta  \\\n",
       "0     121.00  115.424999  5.575        0.048300              0.0000  1.0000   \n",
       "1       0.01    0.005000  0.005        1.000000              2.3182 -0.0001   \n",
       "2       0.00  105.474998  0.000        0.000000              0.0000  0.9999   \n",
       "3       0.00    0.005000  0.000        0.000000              2.1649 -0.0001   \n",
       "4       0.00   95.474998  0.000        0.000000              0.0000  0.9999   \n",
       "...      ...         ...    ...             ...                 ...     ...   \n",
       "2301    0.00  214.500000  0.000        0.000000              0.0000 -1.0000   \n",
       "2302    0.00    3.065000  0.000        0.000000              0.2315  0.0983   \n",
       "2303    0.00  219.525002  0.000        0.000000              0.0000 -1.0000   \n",
       "2304    3.05    2.950000  0.110        0.037288              0.2337  0.0934   \n",
       "2305    0.00  224.525002  0.000        0.000000              0.0000 -1.0000   \n",
       "\n",
       "       gamma   theta    vega     rho  \n",
       "0     0.0000  0.0000  0.0000  0.0164  \n",
       "1     0.0000 -0.0006  0.0000  0.0000  \n",
       "2     0.0000  0.0000  0.0001  0.0180  \n",
       "3     0.0000 -0.0009  0.0001  0.0000  \n",
       "4     0.0000  0.0000  0.0001  0.0197  \n",
       "...      ...     ...     ...     ...  \n",
       "2301  0.0000 -0.0259  0.0000  0.0000  \n",
       "2302  0.0021 -0.0067  0.6436  0.4923  \n",
       "2303  0.0000 -0.0259  0.0000  0.0000  \n",
       "2304  0.0020 -0.0065  0.6205  0.4686  \n",
       "2305  0.0000 -0.0259  0.0000  0.0000  \n",
       "\n",
       "[2306 rows x 29 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(chains)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78f3cc22",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e90f42c6-ab8d-410a-b968-06d36877a815",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
